package br.mikhas.util.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.Collections;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import javax.sql.DataSource;

import br.mikhas.util.Assert;
import br.mikhas.util.jdbc.tx.TransactionUtils;

/**
 * Base of JDBC Util framework.
 * <p>
 * Should be used as a super class to the DAO classes
 * 
 * @author Mikhail Domanoski
 * 
 */
public abstract class JdbcTemplate {

	/**
	 * Current connection being used by the template
	 */
	private DataSource datasource;

	private static final List<Object> EMPTY_LIST = Collections.emptyList();

	/**
	 * Sets the connection that will be used by the template to access the
	 * datasource
	 */
	public void setDataSource(DataSource datasource) {
		if (datasource == null) {
			throw new NullPointerException("The connection may not be null");
		}

		this.datasource = datasource;
	}

	/**
	 * Gets the current datasource
	 * 
	 * @return
	 */
	public DataSource getDataSource() {
		return this.datasource;
	}

	/**
	 * Executes a single SQL statement
	 * 
	 * @param sql
	 *            SQL statement to execute
	 */
	protected void execute(String sql) {
		Statement createStatement = null;
		try {
			Connection connection = this.getConnection();
			createStatement = connection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			createStatement.execute(sql);
		} catch (SQLException e) {
			throw new JdbcException("Error while executing sql statement: "
					+ sql, e);
		} finally {
			JdbcUtils.close(createStatement);
		}
	}

	/**
	 * Creates a prepared statemente from a SQL and an array of arguments to be
	 * binded to the query
	 * 
	 * @param sql
	 *            The SQL statement to be executed
	 * @param parameters
	 *            the parameters that will be binded on the SQL statement
	 */
	protected void execute(String sql, Object... parameters) {
		this.execute(sql, Arrays.asList(parameters));
	}

	/**
	 * Creates a propared statemente from a SQL and a list of arguments to be
	 * ninded to the query
	 * 
	 * @param sql
	 *            The SQL statement to be executed
	 * @param parameters
	 *            the parameters that will be binded on the SQL statement
	 */
	protected void execute(String sql, List<Object> parameters) {
		Assert.notEmpty(sql);
		PreparedStatement stmt;

		stmt = this.createCall(sql, parameters);

		// Executes the SQL statement
		try {
			stmt.execute();
		} catch (SQLException e) {
			throw new JdbcException("Error while executing sql statement: "
					+ sql, e);
		} finally {
			JdbcUtils.close(stmt);
		}
	}

	/**
	 * Query the given sql statement and maps it's results with an row mapper
	 * 
	 * @param sql
	 *            The SQL statement to query
	 * @param mapper
	 *            The object that will map each row
	 * @return the result list containing mapped objects
	 */
	protected <T> List<T> query(String sql, RowMapper<T> mapper) {
		return this.query(sql, EMPTY_LIST, mapper);
	}

	/**
	 * Query the given sql statement, bind an array of parameters no teh
	 * statement and maps it's results with an row mapper
	 * 
	 * @param sql
	 *            The SQL statement to query
	 * @param parameters
	 *            the parameters that will be binded on the SQL statement
	 * @param mapper
	 *            The object that will map each row
	 * @return the result list containing mapped objects
	 */
	protected <T> List<T> query(String sql, Object[] parameters,
			RowMapper<T> mapper) {
		return this.query(sql, Arrays.asList(parameters), mapper);
	}

	/**
	 * Query the given sql statement, bind a list of parameters no teh statement
	 * and maps it's results with an row mapper
	 * 
	 * @param sql
	 *            The SQL statement to query
	 * @param parameters
	 *            the parameters that will be binded on the SQL statement
	 * @param mapper
	 *            The object delegated to extract data from each row of the
	 *            result set
	 * @return the result list containing mapped objects
	 */
	protected <T> List<T> query(String sql, List<?> parameters,
			RowMapper<T> mapper) {
		return this.query(sql, parameters, new RowMapperResultSetExtrator<T>(
				mapper));
	}

	/**
	 * Query the given sql statement, bind a list of parameters no teh statement
	 * and maps it's results with an row mapper
	 * 
	 * @param sql
	 *            The SQL statement to query
	 * @param parameters
	 *            the parameters that will be binded on the SQL statement
	 * @param extrator
	 *            The object delegated to extract data from the result set
	 * @return the result list containing mapped objects
	 */
	protected <T> T query(String sql, List<?> parameters,
			ResultSetExtrator<T> extrator) {

		Assert.notEmpty(sql);
		Assert.notNull(extrator);

		PreparedStatement stmt = this.createStatement(sql, parameters);

		ResultSet resultSet = null;

		// Execute the query
		try {
			resultSet = stmt.executeQuery();

			return extrator.extractData(resultSet);
		} catch (SQLException e) {
			throw new JdbcException("Error while executing sql query: " + sql,
					e);
		} finally {
			JdbcUtils.close(stmt);
		}
	}

	/**
	 * Creates a new prepared statement and sets its parameters if needed
	 * 
	 * @param sql
	 *            The sql statement
	 * @param parameters
	 *            The list of parameters to be setted on the statement
	 * @return a jdbc repared statement
	 */
	private PreparedStatement createStatement(String sql, List<?> parameters) {
		try {
			Connection connection = this.getConnection();
			PreparedStatement statement = connection.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			// Set the parameters
			if (parameters != null && !parameters.isEmpty()) {
				int count = 0;
				Object parameter;

				Iterator<?> iterator = parameters.iterator();
				while (iterator.hasNext()) {
					count++;
					parameter = iterator.next();
					JdbcUtils.setStatementValue(statement, count, parameter);
				}
			}
			return statement;
		} catch (SQLException e) {
			throw new JdbcException("Error while creating sql statement.", e);
		}
	}

	/**
	 * Gets the connection
	 * 
	 * @return The connection
	 * @throws SQLException
	 */
	private Connection getConnection() {
		return TransactionUtils.getConnection(getDataSource());
	}

	/**
	 * Creates a new callable statement and sets its parameters if needed
	 * 
	 * @param sql
	 *            The sql statement
	 * @param parameters
	 *            The list of parameters to be setted on the statement
	 * @return a jdbc callable statement
	 */
	private CallableStatement createCall(String sql, List<Object> parameters) {
		try {
			CallableStatement statement = this.getConnection().prepareCall(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			// Set the parameters
			if (parameters != null && !parameters.isEmpty()) {
				int count = 0;
				Object parameter;

				Iterator<Object> iterator = parameters.iterator();

				while (iterator.hasNext()) {
					count++;
					parameter = iterator.next();
					JdbcUtils.setStatementValue(statement, count, parameter);
				}
			}
			return statement;
		} catch (SQLException e) {
			throw new JdbcException("Error while creating sql statement.", e);
		}
	}

	/**
	 * Shortcut to query a single <code>int</code> value
	 * 
	 * @param sql
	 *            SQL statement that the result <b>must</b> be a single row with
	 *            an integer value
	 * @return The int value
	 */
	protected int queryForInt(String sql) {
		return this.queryForInt(sql, EMPTY_LIST);
	}

	/**
	 * Shortcut to query a single <code>int</code> value
	 * 
	 * @param sql
	 *            SQL statement that the result <b>must</b> be a single row with
	 *            an integer value
	 * @param parameters
	 *            the parameters that will be binded on the SQL statement
	 * @return The int value
	 */
	protected int queryForInt(String sql, Object... parameters) {
		return this.queryForObject(sql, Arrays.asList(parameters),
				Integer.class).intValue();
	}

	/**
	 * Shortcut to query a single <code>int</code> value
	 * 
	 * @param sql
	 *            SQL statement that the result <b>must</b> be a single row with
	 *            an integer value
	 * @param parameters
	 *            the parameters that will be binded on the SQL statement
	 * @return The int value
	 */
	protected int queryForInt(String sql, List<Object> parameters) {
		return this.queryForObject(sql, parameters, Integer.class).intValue();
	}

	/**
	 * Shortcut to query a single {@link Date} value
	 * 
	 * @param sql
	 *            SQL statement that the result <b>must</b> be a single row with
	 *            an {@link Date} value
	 * 
	 * @return The {@link Date} value
	 */
	protected Date queryForDate(String sql) {
		return this.queryForDate(sql, EMPTY_LIST);
	}

	/**
	 * Shortcut to query a single {@link Date} value
	 * 
	 * @param sql
	 *            SQL statement that the result <b>must</b> be a single row with
	 *            an {@link Date} value
	 * @param parameters
	 *            the parameters that will be binded on the SQL statement
	 * @return The {@link Date} value
	 */
	protected Date queryForDate(String sql, Object... parameters) {
		return this.queryForObject(sql, Arrays.asList(parameters), Date.class);
	}

	/**
	 * Shortcut to query a single {@link Date} value
	 * 
	 * @param sql
	 *            SQL statement that the result <b>must</b> be a single row with
	 *            an {@link Date} value
	 * @param parameters
	 *            the parameters that will be binded on the SQL statement
	 * @return The {@link Date} value
	 */
	protected Date queryForDate(String sql, List<Object> parameters) {
		return this.queryForObject(sql, parameters, Date.class);
	}

	/**
	 * Shortcut to query a single {@link String} value
	 * 
	 * @param sql
	 *            SQL statement that the result <b>must</b> be a single row with
	 *            an {@link String} value
	 * @return The {@link String} value
	 */
	protected String queryForString(String sql) {
		return this.queryForString(sql, EMPTY_LIST);
	}

	/**
	 * Shortcut to query a single {@link String} value
	 * 
	 * @param sql
	 *            SQL statement that the result <b>must</b> be a single row with
	 *            an {@link String} value
	 * @param parameters
	 *            the parameters that will be binded on the SQL statement
	 * @return The {@link String} value
	 */
	protected String queryForString(String sql, Object... parameters) {
		return this
				.queryForObject(sql, Arrays.asList(parameters), String.class);
	}

	/**
	 * Shortcut to query a single {@link String} value
	 * 
	 * @param sql
	 *            SQL statement that the result <b>must</b> be a single row with
	 *            an {@link String} value
	 * @param parameters
	 *            the parameters that will be binded on the SQL statement
	 * @return The {@link String} value
	 */
	protected String queryForString(String sql, List<Object> parameters) {
		return this.queryForObject(sql, parameters, String.class);
	}

	/**
	 * Shortcut to query a single {@link Object} value
	 * 
	 * @param sql
	 *            SQL statement that the result <b>must</b> be a single row with
	 *            an {@link String} value
	 * @param parameters
	 *            the parameters that will be binded on the SQL statement
	 * @param type
	 *            the type of the object
	 * @return The {@link Object} value
	 */
	protected <T> T queryForObject(String sql, List<Object> parameters,
			final Class<T> type) {

		RowMapper<T> mapper = new SingleColumnRowMapper<T>(type);

		return (T) this.query(sql, parameters, mapper).get(0);
	}

	/**
	 * Queries the datasource for for a list of objects of the specified type.
	 * 
	 * @param <T>
	 *            The type of the elements on the list
	 * @param sql
	 *            The sql statement to be executed
	 * @param parameters
	 *            The parameters to be set on the statement
	 * @param type
	 *            The class which represents the type of the elements on the
	 *            list
	 * @return A list of objects
	 */
	protected <T> List<T> queryForList(String sql, Object[] parameters,
			Class<T> type) {
		RowMapper<T> mapper = new SingleColumnRowMapper<T>(type);

		return this.query(sql, parameters, mapper);
	}

	/**
	 * Queries the datasource for for a list of objects of the specified type.
	 * 
	 * @param <T>
	 *            The type of the elements on the list
	 * @param sql
	 *            The sql statement to be executed
	 * @param parameters
	 *            The parameters to be set on the statement
	 * @param type
	 *            The class which represents the type of the elements on the
	 *            list
	 * @return A list of objects
	 */
	protected <T> List<T> queryForList(String sql, List<Object> parameters,
			Class<T> type) {
		RowMapper<T> mapper = new SingleColumnRowMapper<T>(type);

		return this.query(sql, parameters, mapper);
	}

	/**
	 * Queries the datasource for for a list of objects of the specified type.
	 * 
	 * @param <T>
	 *            The type of the elements on the list
	 * @param sql
	 *            The sql statement to be executed
	 * @param type
	 *            The class which represents the type of the elements on the
	 *            list
	 * @return A list of objects
	 */
	protected <T> List<T> queryForList(String sql, Class<T> type) {
		RowMapper<T> mapper = new SingleColumnRowMapper<T>(type);

		return this.query(sql, EMPTY_LIST, mapper);
	}

	/**
	 * Queries from datasource and maps a single row from teh result using a
	 * defines {@link RowMapper}
	 * 
	 * @param <T>
	 *            The type of element to be mapped
	 * @param sql
	 *            The SQL statement to execute
	 * @param parameters
	 *            The parameters to be passed to the query
	 * @param mapper
	 *            The row mapper to be used
	 * @return A single mapped result
	 */
	protected <T> T queryForObject(String sql, List<?> parameters,
			RowMapper<T> mapper) {

		return this.query(sql, parameters, new SingleRowResultSetExtractor<T>(
				mapper));
	}

	/**
	 * Queries from datasource and maps a single row from teh result using a
	 * defines {@link RowMapper}
	 * 
	 * @param <T>
	 *            The type of element to be mapped
	 * @param sql
	 *            The SQL statement to execute
	 * @param mapper
	 *            The row mapper to be used
	 * @return A single mapped result
	 */
	protected <T> T queryForObject(String sql, RowMapper<T> mapper) {

		return this.query(sql, EMPTY_LIST, new SingleRowResultSetExtractor<T>(
				mapper));
	}

	/**
	 * Shortcut to query a single {@link Object} value
	 * 
	 * @param sql
	 *            SQL statement that the result <b>must</b> be a single row with
	 *            an {@link String} value
	 * @param parameters
	 *            the parameters that will be binded on the SQL statement
	 * 
	 * @return The {@link Object} value
	 */
	protected Object queryForObject(String sql, Object... parameters) {
		return this.queryForObject(sql, Arrays.asList(parameters),
				(Class<?>) null);
	}
}
